USE Core
GO
IF OBJECT_ID('fEmailIsValid') IS NOT NULL 
BEGIN 
    DROP FUNCTION fEmailIsValid
    IF OBJECT_ID('fEmailIsValid') IS NOT NULL
        PRINT '<<< FAILED DROPPING FUNCTION fEmailIsValid >>>'
    ELSE 
        PRINT '<<< DROPPED FUNCTION fEmailIsValid >>>'
END
GO
CREATE FUNCTION dbo.fEmailIsValid
	(
        @emailAdress varchar(255) = NULL
	)
RETURNS bit
AS
BEGIN
    DECLARE
        @bit bit

    SET @bit = 0
    -- From vyaskn@hotmail.com (http://vyaskn.tripod.com/handling_email_addresses_in_sql_server.htm):
	IF CHARINDEX(' ',LTRIM(RTRIM(@emailAdress))) = 0
        AND LEFT(LTRIM(@emailAdress),1) <> '@'
        AND RIGHT(RTRIM(@emailAdress),1) <> '.'
        AND CHARINDEX('.',@emailAdress,CHARINDEX('@',@emailAdress)) - CHARINDEX('@',@emailAdress) > 1
        AND LEN(LTRIM(RTRIM(@emailAdress))) - LEN(REPLACE(LTRIM(RTRIM(@emailAdress)),'@','')) = 1
        AND CHARINDEX('.',REVERSE(LTRIM(RTRIM(@emailAdress)))) >= 3
        AND (CHARINDEX('.@',@emailAdress) = 0 AND CHARINDEX('..',@emailAdress) = 0)
    BEGIN
        SET @bit = 1
    END
RETURN @bit
END
GO
GRANT EXECUTE ON fEmailIsValid TO public
GO
IF OBJECT_ID('fEmailIsValid') IS NOT NULL 
    PRINT '<<< CREATED FUNCTION fEmailIsValid >>>' 
ELSE 
    PRINT '<<< FAILED CREATING FUNCTION fEmailIsValid >>>' 
GO
 